-- 设置QUOTED_IDENTIFIER选项为ON
SET QUOTED_IDENTIFIER ON;
GO

-- 创建数据库（如果不存在）
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'go_gin')
BEGIN
    CREATE DATABASE go_gin;
END
GO

USE go_gin;
GO

-- 用户表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_user' AND xtype='U')
BEGIN
    CREATE TABLE go_user (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        username nvarchar(50) NOT NULL,
        password nvarchar(255) NOT NULL,
        salt nvarchar(100) NOT NULL,
        nickname nvarchar(50) NULL,
        email nvarchar(100) NULL,
        phone nvarchar(20) NULL,
        avatar nvarchar(255) NULL,
        status int DEFAULT 1,
        remark nvarchar(255) NULL,
        last_login_at nvarchar(30) NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        deleted_at datetime NULL
    );
    
    -- 创建唯一索引
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_go_user_username')
    BEGIN
        CREATE UNIQUE INDEX idx_go_user_username ON go_user(username) WHERE deleted_at IS NULL;
    END
END
GO

-- 角色表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_role' AND xtype='U')
BEGIN
    CREATE TABLE go_role (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        name nvarchar(50) NOT NULL,
        code nvarchar(50) NOT NULL,
        sort int DEFAULT 0,
        status int DEFAULT 1,
        remark nvarchar(255) NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        deleted_at datetime NULL
    );
    
    -- 创建唯一索引
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_go_role_code')
    BEGIN
        CREATE UNIQUE INDEX idx_go_role_code ON go_role(code) WHERE deleted_at IS NULL;
    END
END
GO

-- 部门表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_dept' AND xtype='U')
BEGIN
    CREATE TABLE go_dept (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        name nvarchar(50) NOT NULL,
        parent_id bigint DEFAULT 0,
        sort int DEFAULT 0,
        status int DEFAULT 1,
        remark nvarchar(255) NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        deleted_at datetime NULL
    );
END
GO

-- 菜单表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_menu' AND xtype='U')
BEGIN
    CREATE TABLE go_menu (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        name nvarchar(50) NOT NULL,
        parent_id bigint DEFAULT 0,
        path nvarchar(255) NULL,
        component nvarchar(255) NULL,
        redirect nvarchar(255) NULL,
        icon nvarchar(50) NULL,
        sort int DEFAULT 0,
        status int DEFAULT 1,
        hidden int DEFAULT 0,
        type int DEFAULT 0,
        permission nvarchar(255) NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        deleted_at datetime NULL
    );
END
GO

-- 用户角色关联表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_user_role' AND xtype='U')
BEGIN
    CREATE TABLE go_user_role (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        user_id bigint NOT NULL,
        role_id bigint NOT NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        CONSTRAINT unique_user_role UNIQUE (user_id, role_id)
    );
END
GO

-- 用户部门关联表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_user_dept' AND xtype='U')
BEGIN
    CREATE TABLE go_user_dept (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        user_id bigint NOT NULL,
        dept_id bigint NOT NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        CONSTRAINT unique_user_dept UNIQUE (user_id, dept_id)
    );
END
GO

-- 角色菜单关联表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_role_menu' AND xtype='U')
BEGIN
    CREATE TABLE go_role_menu (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        role_id bigint NOT NULL,
        menu_id bigint NOT NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        CONSTRAINT unique_role_menu UNIQUE (role_id, menu_id)
    );
END
GO

-- 消息表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_message' AND xtype='U')
BEGIN
    CREATE TABLE go_message (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        title nvarchar(100) NOT NULL,
        content nvarchar(1000) NULL,
        type int DEFAULT 0,
        status int DEFAULT 0,
        sender_id bigint NOT NULL,
        receiver_id bigint NOT NULL,
        read_at datetime NULL,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        deleted_at datetime NULL
    );
END
GO

-- 系统设置表
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='go_setting' AND xtype='U')
BEGIN
    CREATE TABLE go_setting (
        id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
        [key] nvarchar(50) NOT NULL,
        value nvarchar(1000) NULL,
        [desc] nvarchar(255) NULL,
        [group] nvarchar(50) NULL,
        type nvarchar(20) NULL,
        required int DEFAULT 0,
        created_at datetime DEFAULT GETDATE(),
        updated_at datetime DEFAULT GETDATE(),
        deleted_at datetime NULL
    );
    
    -- 创建唯一索引
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_go_setting_key')
    BEGIN
        CREATE UNIQUE INDEX idx_go_setting_key ON go_setting([key]) WHERE deleted_at IS NULL;
    END
END
GO

-- 初始化默认管理员账号
IF NOT EXISTS (SELECT * FROM go_user WHERE username = 'admin')
BEGIN
    DECLARE @salt nvarchar(100) = '94d9484c9683520f';
    DECLARE @password nvarchar(255) = '1e87e51c2a8f6b4f34d8158a29c599e1'; -- md5(md5('adminadmin123')+salt)
    
    INSERT INTO go_user (username, password, salt, nickname, status)
    VALUES ('admin', @password, @salt, '管理员', 1);
END
GO

-- 初始化默认角色
IF NOT EXISTS (SELECT * FROM go_role WHERE code = 'admin')
BEGIN
    INSERT INTO go_role (name, code, sort, status, remark)
    VALUES ('管理员', 'admin', 1, 1, '系统管理员');
END
GO

-- 用户角色关联
IF NOT EXISTS (SELECT * FROM go_user_role WHERE user_id = 1 AND role_id = 1)
BEGIN
    INSERT INTO go_user_role (user_id, role_id)
    VALUES (1, 1);
END
GO

-- 初始化默认菜单
IF NOT EXISTS (SELECT * FROM go_menu WHERE name = '系统管理')
BEGIN
    INSERT INTO go_menu (name, parent_id, path, component, icon, sort, status, hidden, type, permission)
    VALUES 
    ('系统管理', 0, '/system', 'Layout', 'setting', 1, 1, 0, 0, NULL),
    ('用户管理', 1, 'user', 'system/user/index', 'user', 1, 1, 0, 1, 'system:user:list'),
    ('角色管理', 1, 'role', 'system/role/index', 'role', 2, 1, 0, 1, 'system:role:list'),
    ('菜单管理', 1, 'menu', 'system/menu/index', 'menu', 3, 1, 0, 1, 'system:menu:list'),
    ('部门管理', 1, 'dept', 'system/dept/index', 'dept', 4, 1, 0, 1, 'system:dept:list'),
    ('系统设置', 1, 'setting', 'system/setting/index', 'setting', 5, 1, 0, 1, 'system:setting:list');
END
GO

-- 关联管理员角色和所有菜单
DECLARE @menu_count INT;
SELECT @menu_count = COUNT(*) FROM go_menu;

DECLARE @i INT = 1;
WHILE @i <= @menu_count
BEGIN
    IF NOT EXISTS (SELECT * FROM go_role_menu WHERE role_id = 1 AND menu_id = @i)
    BEGIN
        INSERT INTO go_role_menu (role_id, menu_id)
        VALUES (1, @i);
    END
    SET @i = @i + 1;
END
GO

-- 初始化系统设置
IF NOT EXISTS (SELECT * FROM go_setting WHERE [key] = 'system_name')
BEGIN
    INSERT INTO go_setting ([key], value, [desc], [group], type, required)
    VALUES 
    ('system_name', 'GinAdmin管理系统', '系统名称', 'system', 'string', 1),
    ('system_logo', 'https://example.com/logo.png', '系统Logo', 'system', 'string', 1),
    ('system_footer', 'Copyright © 2023 GinAdmin', '系统页脚', 'system', 'string', 0);
END
GO

-- 创建用户视图
IF EXISTS (SELECT * FROM sys.views WHERE name = 'v_go_user')
    DROP VIEW v_go_user;
GO

CREATE VIEW v_go_user AS
SELECT u.id, u.username, u.nickname, u.email, u.phone, u.avatar, u.status, 
       u.remark, u.last_login_at, u.created_at, u.updated_at,
       r.name as role_name, r.code as role_code
FROM go_user u
LEFT JOIN go_user_role ur ON u.id = ur.user_id
LEFT JOIN go_role r ON ur.role_id = r.id
WHERE u.deleted_at IS NULL;
GO 